DECLARE @Tmp TABLE (Id INT,Name TEXT) 
INSERT @Tmp SELECT 1,'123 Stack Street' + CHAR(13) + 'Holborn' + CHAR(13) + 'EC1 2QW' 

DECLARE @XML XML = 
(
    SELECT  T.Id AS "@ID",
		  CONVERT(XML,'<PART>' + REPLACE(CAST(Name AS VARCHAR(max)),CHAR(13),'</PART><PART>') + '</PART>') AS AddressParts
    FROM	  @Tmp AS T
    FOR XML PATH('Name'), ROOT('Names'), ELEMENTS, TYPE
)
SELECT  Address1 = FieldAlias.value('(AddressParts/PART)[1]','varchar(max)'),
	   Address2 = FieldAlias.value('(AddressParts/PART)[2]','varchar(max)'),
	   Address3 = FieldAlias.value('(AddressParts/PART)[3]','varchar(max)'),
	   Address3 = FieldAlias.value('(AddressParts/PART)[4]','varchar(max)')
FROM	   @XML.nodes('//Name') AS S(FieldAlias)

